{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1 style=\"color:blue\">Pivot basics</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>temperature</th>\n",
       "      <th>humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>66</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>68</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>75</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>78</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>82</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>beijing</td>\n",
       "      <td>80</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>beijing</td>\n",
       "      <td>77</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>beijing</td>\n",
       "      <td>79</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date      city  temperature  humidity\n",
       "0  5/1/2017  new york           65        56\n",
       "1  5/2/2017  new york           66        58\n",
       "2  5/3/2017  new york           68        60\n",
       "3  5/1/2017    mumbai           75        80\n",
       "4  5/2/2017    mumbai           78        83\n",
       "5  5/3/2017    mumbai           82        85\n",
       "6  5/1/2017   beijing           80        26\n",
       "7  5/2/2017   beijing           77        30\n",
       "8  5/3/2017   beijing           79        35"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv(\"weather.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">temperature</th>\n",
       "      <th colspan=\"3\" halign=\"left\">humidity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>beijing</th>\n",
       "      <td>80</td>\n",
       "      <td>77</td>\n",
       "      <td>79</td>\n",
       "      <td>26</td>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>75</td>\n",
       "      <td>78</td>\n",
       "      <td>82</td>\n",
       "      <td>80</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>65</td>\n",
       "      <td>66</td>\n",
       "      <td>68</td>\n",
       "      <td>56</td>\n",
       "      <td>58</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         temperature                   humidity                  \n",
       "date        5/1/2017 5/2/2017 5/3/2017 5/1/2017 5/2/2017 5/3/2017\n",
       "city                                                             \n",
       "beijing           80       77       79       26       30       35\n",
       "mumbai            75       78       82       80       83       85\n",
       "new york          65       66       68       56       58       60"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='city',columns='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>beijing</th>\n",
       "      <td>26</td>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>80</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>56</td>\n",
       "      <td>58</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "date      5/1/2017  5/2/2017  5/3/2017\n",
       "city                                  \n",
       "beijing         26        30        35\n",
       "mumbai          80        83        85\n",
       "new york        56        58        60"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='city',columns='date',values=\"humidity\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">temperature</th>\n",
       "      <th colspan=\"3\" halign=\"left\">humidity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th>beijing</th>\n",
       "      <th>mumbai</th>\n",
       "      <th>new york</th>\n",
       "      <th>beijing</th>\n",
       "      <th>mumbai</th>\n",
       "      <th>new york</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5/1/2017</th>\n",
       "      <td>80</td>\n",
       "      <td>75</td>\n",
       "      <td>65</td>\n",
       "      <td>26</td>\n",
       "      <td>80</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5/2/2017</th>\n",
       "      <td>77</td>\n",
       "      <td>78</td>\n",
       "      <td>66</td>\n",
       "      <td>30</td>\n",
       "      <td>83</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5/3/2017</th>\n",
       "      <td>79</td>\n",
       "      <td>82</td>\n",
       "      <td>68</td>\n",
       "      <td>35</td>\n",
       "      <td>85</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         temperature                 humidity                \n",
       "city         beijing mumbai new york  beijing mumbai new york\n",
       "date                                                         \n",
       "5/1/2017          80     75       65       26     80       56\n",
       "5/2/2017          77     78       66       30     83       58\n",
       "5/3/2017          79     82       68       35     85       60"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='date',columns='city')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">date</th>\n",
       "      <th colspan=\"3\" halign=\"left\">temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th>beijing</th>\n",
       "      <th>mumbai</th>\n",
       "      <th>new york</th>\n",
       "      <th>beijing</th>\n",
       "      <th>mumbai</th>\n",
       "      <th>new york</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>humidity</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>79.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>None</td>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>None</td>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>None</td>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              date                     temperature                \n",
       "city       beijing    mumbai  new york     beijing mumbai new york\n",
       "humidity                                                          \n",
       "26        5/1/2017      None      None        80.0    NaN      NaN\n",
       "30        5/2/2017      None      None        77.0    NaN      NaN\n",
       "35        5/3/2017      None      None        79.0    NaN      NaN\n",
       "56            None      None  5/1/2017         NaN    NaN     65.0\n",
       "58            None      None  5/2/2017         NaN    NaN     66.0\n",
       "60            None      None  5/3/2017         NaN    NaN     68.0\n",
       "80            None  5/1/2017      None         NaN   75.0      NaN\n",
       "83            None  5/2/2017      None         NaN   78.0      NaN\n",
       "85            None  5/3/2017      None         NaN   82.0      NaN"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='humidity',columns='city')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1 style=\"color:blue\">Pivot Table</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>temperature</th>\n",
       "      <th>humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>61</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>72</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>75</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>78</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>82</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>80</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date      city  temperature  humidity\n",
       "0  5/1/2017  new york           65        56\n",
       "1  5/1/2017  new york           61        54\n",
       "2  5/2/2017  new york           70        60\n",
       "3  5/2/2017  new york           72        62\n",
       "4  5/1/2017    mumbai           75        80\n",
       "5  5/1/2017    mumbai           78        83\n",
       "6  5/2/2017    mumbai           82        85\n",
       "7  5/2/2017    mumbai           80        26"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"weather2.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">humidity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>81.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>76.5</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>55.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         humidity          temperature         \n",
       "date     5/1/2017 5/2/2017    5/1/2017 5/2/2017\n",
       "city                                           \n",
       "mumbai       81.5     55.5        76.5     81.0\n",
       "new york     55.0     61.0        63.0     71.0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=\"city\",columns=\"date\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 style=\"color:brown\">Margins</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">humidity</th>\n",
       "      <th colspan=\"4\" halign=\"left\">temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "      <th>All</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>beijing</th>\n",
       "      <td>26.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>236.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>80.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>110.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>216.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>631.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>807.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         humidity                          temperature                    \\\n",
       "date     5/1/2017 5/2/2017 5/3/2017    All    5/1/2017 5/2/2017 5/3/2017   \n",
       "city                                                                       \n",
       "beijing      26.0     30.0     35.0   91.0        80.0     77.0     79.0   \n",
       "mumbai       80.0     83.0     85.0  248.0        75.0     78.0     82.0   \n",
       "new york    110.0    122.0     60.0  292.0       126.0    142.0     68.0   \n",
       "All         216.0    235.0    180.0  631.0       281.0    297.0    229.0   \n",
       "\n",
       "                 \n",
       "date        All  \n",
       "city             \n",
       "beijing   236.0  \n",
       "mumbai    235.0  \n",
       "new york  336.0  \n",
       "All       807.0  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=\"city\",columns=\"date\", margins=True,aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 style=\"color:brown\">Grouper</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>temperature</th>\n",
       "      <th>humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>61</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>30</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>28</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12/3/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>25</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date      city  temperature  humidity\n",
       "0   5/1/2017  new york           65        56\n",
       "1   5/2/2017  new york           61        54\n",
       "2   5/3/2017  new york           70        60\n",
       "3  12/1/2017  new york           30        50\n",
       "4  12/2/2017  new york           28        52\n",
       "5  12/3/2017  new york           25        51"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"weather3.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['date'] = pd.to_datetime(df['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>humidity</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th>new york</th>\n",
       "      <th>new york</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-05-31</th>\n",
       "      <td>56.666667</td>\n",
       "      <td>65.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>51.000000</td>\n",
       "      <td>27.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             humidity temperature\n",
       "city         new york    new york\n",
       "date                             \n",
       "2017-05-31  56.666667   65.333333\n",
       "2017-12-31  51.000000   27.666667"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=pd.Grouper(freq='M',key='date'),columns='city')"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
